/**
 * 
 */
package com.clh.dao.impl;

import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.clh.dao.RepairDao;
import com.clh.entity.Owner;
import com.clh.entity.Pay;
import com.clh.entity.Repair;
import com.clh.util.DBUtil;

import javafx.scene.control.IndexRange;

/**
  * @Description: TODO
  * @author zyw
  * @date 2022-5-25 20:21:59
  * @version v1.0
 */
public class RepairDaoImpl implements RepairDao{

	@Override
	public List<Repair> findRepairList(Repair repair, int pageNum, int pageSize) {
		List<Repair> list = new ArrayList<Repair>();
		
		StringBuffer sql = new StringBuffer();
		sql.append(" select")
		   .append(" A.repair_no, ")
		   .append(" C.room_no, ")
		   .append(" A.repair_create_time, ")
		   .append(" A.repair_content, ")
		   .append(" A.repair_status, ")
		   .append(" A.repair_exchange_time, ")
		   .append(" A.repair_conductor, ")
		   .append(" A.pay_no ")
		   .append(" from repair A ")
		  
		   .append(" inner join owner C ")
		   .append("  on A.room_no = C.room_no ")
		   .append(" where 1=1 ");
		
		if(repair.getRepairCreateTime() != null && !"".equals(repair.getRepairCreateTime())) {
			sql.append(" and A.repair_create_time >= '"+repair.getRepairCreateTime()+"'");
		}
		if(repair.getRepairDealTime() != null && !"".equals(repair.getRepairDealTime())) {
			sql.append(" and A.repair_deal_time <= '"+repair.getRepairDealTime()+"'");
			
		}
		if(repair.getRepairNO() != null && !"".equals(repair.getRepairNO())) {
			
			sql.append(" and repair_no like '"+repair.getRepairNO()+"'");
			
		}
		sql.append(" limit "+(pageNum-1)*pageSize+","+pageSize);
		
		ResultSet rs = DBUtil.query(sql.toString());
		
		try {
			while(rs.next()) {
				Repair repair1 = new Repair();
				repair1.setRepairNO(rs.getString("repair_no"));
				repair1.setRoomNo(rs.getString("room_no"));
				
				repair1.setRepairCreateTime(rs.getString("repair_create_time"));
				repair1.setRepairContent(rs.getString("repair_content"));
				repair1.setRepairStatus(rs.getString("repair_status"));
				repair1.setRepairDealTime(rs.getString("repair_exchange_time"));
				repair1.setRepairHandler(rs.getString("repair_conductor"));
				repair1.setPayNo(rs.getString("pay_no"));
				
				Owner owner = new Owner();
				owner.setOwnerId(rs.getString("room_no"));
				repair1.setOwner(owner);
				
				Pay pay = new Pay();
				pay.setPayNo(rs.getString("pay_no"));
				repair1.setPay(pay);
				
				list.add(repair1);
				
				
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	//报修信息的总条数
	
	@Override
	public int countByCondition(String start, String end, String repairNo) {
		int n = 0;
		StringBuffer sql = new StringBuffer();
		sql .append(" select")
		    .append(" count(*)")
		    .append(" from repair A")
		    
		    .append(" inner join owner C")
		    .append(" on A.room_no = C.room_no ")
		    .append(" where 1=1 ");
		
		if(start != null && !"".equals(start)) {
			sql.append(" and A.repair_create_time >= '"+start+"'");
		}
		if(end != null && !"".equals(end)) {
			sql.append(" and A.repair_deal_time <= '"+end+"'");
			
		}
		if(repairNo != null && !"".equals(repairNo)) {
			sql.append(" and A.repair_no like '"+repairNo+"'");
		}
		ResultSet rs = DBUtil.query(sql.toString());
		
		try {
			while(rs.next()) {
				n = rs.getInt(1);
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return n;
	}

	//根据报修编号查保修信息
	@Override
	public Repair queryByIdRepairInformation(String repairNo) {
		Repair repair = null;
		StringBuffer sql = new StringBuffer();
		sql.append(" select")
		   .append(" A.repair_no, ")
		   .append(" C.room_no, ")
		   .append(" A.repair_create_time, ")
		   .append(" A.repair_content, ")
		   .append(" A.repair_status, ")
		   .append(" A.repair_exchange_time, ")
		   .append(" A.repair_conductor, ")
		   .append(" A.pay_no ")
		   .append(" from repair A ")
		   
		   .append(" inner join owner C ")
		   .append("  on A.room_no = C.room_no ")
		   .append(" where repair_no = ?");
		
		ResultSet rs = DBUtil.query(sql.toString(), repairNo);
		try {
			while(rs.next()) {
				repair = new Repair();
				repair.setRepairNO(rs.getString("repair_no"));
				repair.setRoomNo(rs.getString("room_no"));
				
				repair.setRepairCreateTime(rs.getString("repair_create_time"));
				repair.setRepairContent(rs.getString("repair_content"));
				repair.setRepairStatus(rs.getString("repair_status"));
				repair.setRepairDealTime(rs.getString("repair_exchange_time"));
				repair.setRepairHandler(rs.getString("repair_conductor"));
				repair.setPayNo(rs.getString("pay_no"));
				
				Owner owner = new Owner();
				owner.setOwnerId(rs.getString("room_no"));
				repair.setOwner(owner);
				
				Pay pay = findPayMoney(repairNo);
				
				
				repair.setPay(pay);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return repair;
	}

	
	//修改报修信息
	@Override
	public int updateRepair(String repairHandler, String repairStatus, String repairNo, String repairDealTime) {
		StringBuffer sql = new StringBuffer();
		sql.append(" update ")
			.append(" repair ")
			.append(" set ")
			.append("  repair_conductor = ?,")
			.append(" repair_exchange_time = ?,")
			.append(" repair_status = ?")
			.append(" where repair_no = ?");

		return DBUtil.update(sql.toString(), repairHandler,repairDealTime,repairStatus,repairNo);
	}

	//报修信息删除
	@Override
	public int deleteReapir(String repairNo) {
		StringBuffer sql = new StringBuffer();
		sql.append(" delete ")
			.append(" from")
			.append(" repair ")
			.append(" where repair_no = ?");
		int n = DBUtil.update(sql.toString(), repairNo);
		
		return n;
	}

	//单个用户的报修信息
	@Override
	public List<Repair> ownerRepairList(String phoneNumber, String repairContent, int pageNum, int pageSize) {
		List<Repair> list = new ArrayList<Repair>();
		StringBuffer sql = new StringBuffer();
		sql.append(" select")
		   .append(" * from")
		   .append(" repair")
		   .append(" where room_no =( ")
		   .append(" select ")
		   .append(" room_no")
		   .append(" from ")
		   .append(" owner")
		   .append(" where owner_phoneNumber = '"+phoneNumber+"')");
		
		if(repairContent != null && !"".equals(repairContent)) {
			sql.append(" and repair_content like '"+repairContent+"'");
		}
		sql.append(" limit "+(pageNum-1)*pageSize+","+pageSize);
		ResultSet rs = DBUtil.query(sql.toString());
		
		try {
			while(rs.next()) {
				Repair repair = new Repair();
				repair.setRepairNO(rs.getString("repair_no"));
				repair.setRoomNo(rs.getString("room_no"));
				
				repair.setRepairCreateTime(rs.getString("repair_create_time"));
				repair.setRepairContent(rs.getString("repair_content"));
				repair.setRepairStatus(rs.getString("repair_status"));
				repair.setRepairDealTime(rs.getString("repair_exchange_time"));
				repair.setRepairHandler(rs.getString("repair_conductor"));
				repair.setPayNo(rs.getString("pay_no"));
				
				Owner owner = new Owner();
				owner.setOwnerId(rs.getString("room_no"));
				repair.setOwner(owner);
				
				Pay pay = new Pay();
				pay.setPayNo(rs.getString("pay_no"));
				repair.setPay(pay);
				list.add(repair);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return list;
	}

	@Override
	public int ownerCountByCondition(String phoneNumber, String repairContent) {
		int n = 0;
		StringBuffer sql = new StringBuffer();
		sql.append("select count(*) FROM repair   WHERE  room_no = 	 (select room_no from owner where owner_phoneNumber = '"+phoneNumber+"')");
		if(repairContent != null && !"".equals(repairContent)) {
			sql.append(" and repair_content = '"+repairContent+"'");
		}
		
		ResultSet rs = DBUtil.query(sql.toString());
		
		try {
			while(rs.next()) {
				n = rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return n;
	}

	
	//用户报修状态修改
	@Override
	public int wonerRepairStatusUpdate(String repairNo, String repairStatus) {
		StringBuffer sql = new StringBuffer();
		sql.append(" update repair set repair_status = ? where repair_no = ?");
		
		int n = DBUtil.update(sql.toString(), repairStatus,repairNo);
		
		return n;
	}

	@Override
	public int ownerRepairAdd(Repair repair) {
		StringBuffer sql = new StringBuffer();
		sql.append(" insert into repair (repair_no, ")
		.append(" room_no, ")
		.append(" repair_create_time, ")
		.append(" repair_content, ")
		.append(" repair_status, ")
		.append(" repair_exchange_time, ")
		.append(" repair_conductor, ")
		.append(" pay_no)")
		.append(" values(?,?,?,?,?,?,?,?)");
		
		int n = DBUtil.update(sql.toString(), repair.getRepairNO(),repair.getRoomNo(),repair.getRepairCreateTime(),
				repair.getRepairContent(),repair.getRepairStatus(),repair.getRepairDealTime(),repair.getRepairHandler(),repair.getPayNo());
		
		return n;
	}

	@Override
	public int ownerReapirPay(Pay pay) {
		StringBuffer sql = new StringBuffer();
		sql.append(" insert into pay(pay_no, room_no, pay_start_time, pay_end_time, pay_type, pay_money, pay_time, pay_status)")
		.append(" values(?,?,?,?,?,?,?,?)");
		
		int n = DBUtil.update(sql.toString(), pay.getPayNo(),pay.getRoomNo(), pay.getPayStartTime(),pay.getPayEndTime(), pay.getPayType().getTypeId(), pay.getPayMoney(), pay.getPayTime(),pay.getPayStatus());
		return n;
	}

	
	//缴费订单号插入报修表
	@Override
	public int ownerRepairPayNoAdd(String repairNo, String payNo) {
		StringBuffer sql = new StringBuffer();
		sql.append(" update repair set pay_no = ? where repair_no = ?");
		
		int n = DBUtil.update(sql.toString(),payNo, repairNo );
		return n;
	}

	//通过报修编号查缴费金额
	@Override
	public Pay findPayMoney(String repairNo) {
		StringBuffer sql = new StringBuffer();
		sql.append("  select * from pay where pay_no = (select pay_no from repair where repair_no = ? ) ");
		ResultSet rs = DBUtil.query(sql.toString(), repairNo);
		Pay pay = new Pay();
		try {
			while(rs.next()) {
				
				pay.setPayNo(rs.getString("pay_no"));
				pay.setPayMoney(rs.getBigDecimal("pay_money"));
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return pay;
	}

	
}
